﻿/****************************** Module Header ******************************\
* Module Name:	Program.cs
* Project:		CSUseADONET
* Copyright (c) Microsoft Corporation.
* 
* The CSUseADONET sample demonstrates the Microsoft ADO.NET technology to 
* access databases using Visual C#. It shows the basic structure of 
* connecting to a data source, issuing SQL commands, using DataSet object  
* and performing the cleanup. 
* 
* This source is subject to the Microsoft Public License.
* See http://www.microsoft.com/opensource/licenses.mspx#Ms-PL.
* All other rights reserved.
* 
* History:
* * 2/25/2009 09:00 PM Lingzhi Sun Created
\***************************************************************************/

#region Using directives
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
using System.Xml;
#endregion


class Program
{
    static void Main(string[] args)
    {
        SqlConnection conn = null;

        try
        {
            /////////////////////////////////////////////////////////////////
            // Connect to the data source.
            // 

            Console.WriteLine("Connecting to the database ...");

            // Get the connection string from App.config. (The data source is
            // created in the sample SQLServer2005DB)
            string connStr = ConfigurationManager.ConnectionStrings[
                "SQLServer2005DB"].ConnectionString;
            
            // Open the connection
            conn = new SqlConnection(connStr);
            conn.Open();


            /////////////////////////////////////////////////////////////////
            // Build and Execute an ADO.NET Command.
            // It can be a SQL statement (SELECT/UPDATE/INSERT/DELETE), or a  
            // stored procedure call. Here is the sample of an INSERT command.
            // 

            Console.WriteLine("Inserting a record to the Student table");

            // 1. Create a command object
            SqlCommand cmd = new SqlCommand();

            // 2. Assign the connection to the command
            cmd.Connection = conn;

            // 3. Set the command text
            // SQL statement or the name of the stored procedure 
            cmd.CommandText = "INSERT INTO Student(ID, Name, Image) VALUES" + 
                " (@ID, @Name, @Image)";

            // 4. Set the command type
            // CommandType.Text for oridinary SQL statements; 
            // CommandType.StoredProcedure for stored procedures.
            cmd.CommandType = CommandType.Text;

            // 5. Append the parameters
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = 123456;
            cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 255).Value = 
                "Lingzhi Sun";
            
            // Read the image file into an arrary of bytes
            byte[] bImage = ReadImage(@"MSDN.jpg");

            // When sending a null value as a Parameter value in a 
            // command to the database, you cannot use null. Instead 
            // you need to use DBNull.Value
            cmd.Parameters.Add("@Image", SqlDbType.Image).Value =
                bImage == null ? DBNull.Value : (object)bImage;

            // 6. Execute the command
            cmd.ExecuteNonQuery();


            /////////////////////////////////////////////////////////////////
            // Use the DataSet Object.
            // The DataSet, which is an in-memory cache of data retrieved 
            // from a data source, is a major component of the ADO.NET 
            // architecture. The DataSet consists of a collection of 
            // DataTable objects that you can relate to each other with 
            // DataRelation objects.
            // 

            // 1. Create a DataSet object
            DataSet ds = new DataSet();

            // 2. Create a SELECT SQL command
            string strSelectCmd = "SELECT * FROM Student"; 

            // 3. Create a SqlDataAdapter object
            // SqlDataAdapter represents a set of data commands and a 
            // database connection that are used to fill the DataSet and 
            // update a SQL Server database. 
            SqlDataAdapter da = new SqlDataAdapter(strSelectCmd, conn);

            // 4. Fill the DataSet object
            // Fill the DataTable named "Student" in DataSet with the rows
            // selected by the SQL statement.
            da.Fill(ds, "Student");

            // 5. Display each row of data in the "Student" data table 
            foreach (DataRow row in ds.Tables["Student"].Rows)
            {
                // When dumping SQL-Nullable field in the DataTable, test it
                // for System.DBNull type.
                Console.WriteLine("{0}\t{1}", row["ID"], 
                    row["Name"] == DBNull.Value ? "(DBNull)" : row["Name"]);
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(
                "The application throws the error: {0}", ex.Message);
        }
        finally
        {
            /////////////////////////////////////////////////////////////////
            // Clean up objects before exit.
            // 

            Console.WriteLine("Closing the connections ...");

            // Close the connection to the database if it is open
            if (conn != null && conn.State == ConnectionState.Open)
                conn.Close();
        }
    }

    /// <summary>
    /// Read an image file to an array of bytes.
    /// </summary>
    /// <param name="path">The path of the image file.</param>
    /// <returns>The output of the array.</returns>
    private static byte[] ReadImage(string path)
    {
        try
        {
            // Open the image file
            using (FileStream fs = new FileStream(path, FileMode.Open,
                FileAccess.Read))
            {
                // Create an array of bytes
                byte[] bPicture = new byte[fs.Length];

                // Read the image file 
                fs.Read(bPicture, 0, Convert.ToInt32(fs.Length));

                return bPicture;
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(
                "The application throws the error: {0}", ex.Message);
            return null;
        }
    }
}

